package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;

import modelo_relacional.Carro;
import modelo_relacional.Obra;
import dao_framework.InsertUpdateDeleteDaoException;
import dao_framework.daoHelper;
import dao_framework.dbConn;

public class ObraDao {

	private dbConn connDB;
	private daoHelper helper;
	private final String dmlInsert;
	private final String dmlDelete;
	private final String dmlUpdate;
	
	public ObraDao() {
		connDB = new dbConn();
		helper = new daoHelper();
		dmlInsert = "insert into Obra (Carro_ID, Kms_Obra, Descricao) values (?,?,?)";
		dmlDelete = "delete from obra where obra_id = ?";
		dmlUpdate = "update obra set carro_ID = ?, kms_obra = ?, descricao = ?, Data_Ult_Alteracao = ? where obra_id = ?";
	}
	
	public void dmlObra(Obra obra, Carro carro, String dml) {
		Connection conn = null;
		int obraID = 0;
		
		try {
			connDB.beginTransaction();
			conn = connDB.getConnectionFromContext();
			
			String timeStamp = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(Calendar.getInstance().getTime());
			
			if (dml.equals("Insert")) {
				obraID = helper.insertDelUpdReturnGenKey(conn, dmlInsert, Integer.toString(carro.getCarro_id()),obra.getKms_obra(),obra.getDesc());
				obra.setObra_id(obraID);
			}
			else if (dml.equals("Delete")) {
				helper.insertDelUpd(conn, dmlDelete, Integer.toString(obra.getObra_id()));
			}
			else if (dml.equals("Update")) {
				helper.insertDelUpd(conn, dmlUpdate, Integer.toString(obra.getCarro_id()),obra.getKms_obra(),obra.getDesc(),timeStamp,Integer.toString(obra.getObra_id()));
			}
			
			connDB.endTransaction();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			connDB.rollbackTransaction();
			throw new InsertUpdateDeleteDaoException("Nao foi possivel realizar a transac��o "+obra,e);
		}
		finally {
			connDB.close(conn);
		}
		
	}

	public List<Obra> returnObraData (Obra obra) {
		Connection conn = null;
		PreparedStatement stm = null;
		ResultSet resultset = null;
		String dmlSelect = "select * from obra where carro_id = ? order by obra_id asc";
		List<Obra> obras = new ArrayList<Obra>();
		
		try {
			connDB.beginTransaction();
			conn = connDB.getConnectionFromContext();
			
			obras = setList(helper.getDados(conn,dmlSelect,obra.getCarro_id()));
			
			connDB.endTransaction();
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally {
			connDB.closeAll(stm, conn);
			connDB.close(resultset);
		}
		
		return obras;
	}
	
	public List<Obra> setList (ResultSet rset) {
		List<Obra> obras = new ArrayList<Obra>();
		
		try {
			while (rset.next()) {
				Obra obra = new Obra();
				obra.setObra_id(rset.getInt("Obra_id"));
				obra.setCarro_id(rset.getInt("Carro_id"));
				obra.setKms_obra(rset.getString("Kms_Obra"));
				obra.setDesc(rset.getString("Descricao"));
				obra.setData_criacao(rset.getString("Data_Criacao"));
				obras.add(obra);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally {
			connDB.close(rset);
		}
		
		return obras;
	}
}
